//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;
using Microsoft.Research.ScientificWorkflow;
using System.Threading;
using System.Data.Common;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Microsoft.Research.ScientificWorkflow.Activities
{
    /// <summary>
    /// This activity enumerates the Stored Procedure details for the 
    /// database.
    /// This activity creates a Windows Form for showing the DB details.
    /// </summary>
    [Name("Enumerate Stored Procedures for DB", "StoredProc Enumerator")]
    [Description("Retrieves the names and details for the stored procedures in the DB.")]
    [WorkflowCategory("Database")]
    class StoreProcedureRetriever : Activity
    {
        #region Member Variables

        /// <summary>
        /// Connection String with format specifiers to connect to the
        /// database instance.
        /// </summary>
        private const string connectionString = @"Data Source={0},1433;Network Library=DBMSSOCN;Database={1};Trusted_Connection=yes;";

        /// <summary>
        /// Form for displaying the stored procedure details.
        /// </summary>
        private Form spDetailsForm;

        /// <summary>
        /// Tree View Control for displaying the names of Stored Procedures
        /// </summary>
        private TreeView spTree;

        /// <summary>
        /// Grid for displaying the parameter details for the Stored Proc
        /// </summary>
        DataGridView paramGrid;

        /// <summary>
        /// OK Button for closing the window
        /// </summary>
        private Button CloseBtn;

        /// <summary>
        /// Button for executing the stored procedure.
        /// </summary>
        private Button ExecuteBtn;

        #endregion

        #region Property Definitions

        public static DependencyProperty DatabaseConnectionProperty = DependencyProperty.Register("DatabaseConnection",
            typeof(SqlConnection), typeof(StoreProcedureRetriever));

        /// <summary>
        /// Database Server Connection Object.
        /// </summary>
        [RequiredInputParam]
        [Name("Database Connection")]
        [Description("Connection to the database server.")]
        public SqlConnection DatabaseConnection
        {
            get { return ((SqlConnection)(base.GetValue(StoreProcedureRetriever.DatabaseConnectionProperty))); }
            set { base.SetValue(StoreProcedureRetriever.DatabaseConnectionProperty, value); }
        }

        #endregion

        #region Overridden Methods

        /// <summary>
        /// Spawns a new thread which launches the SP Details Window.
        /// </summary>
        /// <param name="executionContext"></param>
        /// <returns></returns>
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            // We need the window we show to operate in its own STA thread
            Thread spWindow = new Thread(new ThreadStart(ThreadStartingPoint));
            spWindow.SetApartmentState(ApartmentState.STA);
            spWindow.IsBackground = true;
            spWindow.Start();

            return ActivityExecutionStatus.Closed;
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Thread Proc which creates an instance of the window and adds
        /// the child controls.
        /// </summary>
        private void ThreadStartingPoint()
        {
            CreateWindow();
            System.Windows.Threading.Dispatcher.Run();
        }

        /// <summary>
        /// Creates a new window for displaying the SP details and adds
        /// child controls to that window.
        /// </summary>
        private void CreateWindow()
        {
            this.spDetailsForm = new Form();
            this.spTree = new System.Windows.Forms.TreeView();
            this.paramGrid = new System.Windows.Forms.DataGridView();
            this.CloseBtn = new Button();
            this.ExecuteBtn = new Button();
            ((System.ComponentModel.ISupportInitialize)(this.paramGrid)).BeginInit();
            this.spDetailsForm.SuspendLayout();
            // 
            // close button
            // 
            this.CloseBtn.Location = new System.Drawing.Point(215, 550);
            this.CloseBtn.Name = "Close";
            this.CloseBtn.Size = new System.Drawing.Size(137, 23);
            this.CloseBtn.TabIndex = 2;
            this.CloseBtn.Text = "Close";
            this.CloseBtn.UseVisualStyleBackColor = true;
            this.CloseBtn.Click += new System.EventHandler(this.close_Click);
            // 
            // execute button
            // 
            this.ExecuteBtn.Location = new System.Drawing.Point(380, 549);
            this.ExecuteBtn.Name = "Execute";
            this.ExecuteBtn.Size = new System.Drawing.Size(145, 23);
            this.ExecuteBtn.TabIndex = 3;
            this.ExecuteBtn.Text = "Execute";
            this.ExecuteBtn.UseVisualStyleBackColor = true;
            this.ExecuteBtn.Click += new System.EventHandler(this.ExecuteBtn_Click);            
            // 
            // treeView
            // 
            this.spTree.Location = new System.Drawing.Point(12, 12);
            this.spTree.Name = "spTree";
            this.spTree.Size = new System.Drawing.Size(161, 561);
            this.spTree.TabIndex = 0;
            this.spTree.NodeMouseClick += new System.Windows.Forms.TreeNodeMouseClickEventHandler(this.spTree_NodeMouseClick);
            // 
            // dataGridView
            // 
            this.paramGrid.AllowUserToDeleteRows = false;
            this.paramGrid.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.paramGrid.Location = new System.Drawing.Point(180, 13);
            this.paramGrid.Name = "paramGrid";
            this.paramGrid.ReadOnly = false;
            this.paramGrid.Size = new System.Drawing.Size(355, 531);
            this.paramGrid.TabIndex = 1;
            // 
            // SPDetails
            // 
            this.spDetailsForm.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.spDetailsForm.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.spDetailsForm.ClientSize = new System.Drawing.Size(547, 584);
            this.spDetailsForm.Controls.Add(this.ExecuteBtn);
            this.spDetailsForm.Controls.Add(this.CloseBtn);
            this.spDetailsForm.Controls.Add(this.paramGrid);
            this.spDetailsForm.Controls.Add(this.spTree);
            this.spDetailsForm.Name = "SPDetails";
            this.spDetailsForm.Text = "SPDetails";
            this.spDetailsForm.Load += new System.EventHandler(this.SPDetails_Load);
            ((System.ComponentModel.ISupportInitialize)(this.paramGrid)).EndInit();
            this.spDetailsForm.ResumeLayout(false);
            this.spDetailsForm.Show();
        }

        /// <summary>
        /// Event handler called when the window is being loaded.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void SPDetails_Load(object sender, EventArgs e)
        {
            InitTree();

            InitGrid();
        }

        /// <summary>
        /// Event handler for tree view item clicked.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void spTree_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            // Get the parameters only when a stored proc node is selected.
            if (0 == e.Node.Nodes.Count)
                LoadData(e.Node.Text);
            else
                this.paramGrid.Rows.Clear();
        }

        /// <summary>
        /// Event handler for button click.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void close_Click(object sender, EventArgs e)
        {
            this.spDetailsForm.Close();
        }

        /// <summary>
        /// Event handler for execute button click.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ExecuteBtn_Click(object sender, EventArgs e)
        {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < this.paramGrid.Rows.Count - 1; ++i)
            {
                str.Append(this.paramGrid.Rows[i].Cells[0].Value.ToString().Remove(0,1));
                str.Append(",");
                str.Append(this.paramGrid.Rows[i].Cells[1].Value.ToString());
                str.Append(",");
                str.Append(this.paramGrid.Rows[i].Cells[2].Value.ToString());
                str.Append((i == this.paramGrid.Rows.Count - 2) ? "" : ",");
            }

            DatabaseHelper.Init();
            try
            {
                SqlDataReader rdr = DatabaseHelper.ExecuteStoredProc(this.DatabaseConnection,
                    this.spTree.SelectedNode.Text, str.ToString());
                rdr.Close();
                MessageBox.Show("Procedure executed successfully");
            }
            catch (Exception)
            {
                MessageBox.Show("Error occured while executing the stored procedure.");
            }
        }

        /// <summary>
        /// Adds new child nodes for the tree node passed.
        /// </summary>
        /// <param name="node"></param>
        /// <param name="names"></param>
        private void AddChildNodes(ref TreeNode node, List<string> names)
        {
            foreach (string str in names)
            {
                node.Nodes.Add(str.Substring(0, str.IndexOf(';')));
            }
        }

        /// <summary>
        /// This method initialized the grid control.
        /// </summary>
        private void InitGrid()
        {
            DataGridViewCell cell = new DataGridViewTextBoxCell();
            cell.Style.BackColor = System.Drawing.Color.DimGray;

            DataGridViewColumn colNames = new DataGridViewColumn();
            colNames.CellTemplate = cell;
            colNames.HeaderText = "ParamName";
            this.paramGrid.Columns.Add(colNames);


            DataGridViewColumn colTypes = new DataGridViewColumn();
            colTypes.CellTemplate = cell;
            colTypes.HeaderText = "ParamType";
            this.paramGrid.Columns.Add(colTypes);

            DataGridViewColumn colValues = new DataGridViewColumn();
            colValues.CellTemplate = cell;
            colValues.HeaderText = "ParamValue";
            colValues.ReadOnly = false;
            this.paramGrid.Columns.Add(colValues);
        }

        /// <summary>
        /// Initializes the tree view control and adds nodes for
        /// each SP.
        /// </summary>
        private void InitTree()
        {
            TreeNode node = spTree.Nodes.Add("Database - " + this.DatabaseConnection.Database);

            TreeNode system = node.Nodes.Add("System SP's");
            TreeNode dbo = node.Nodes.Add("DBO SP's");

            List<string> sysProc; List<string> dboProc;
            ReadSQLMetaData(out dboProc, out sysProc);

            AddChildNodes(ref system, sysProc);
            AddChildNodes(ref dbo, dboProc);
        }

        /// <summary>
        /// This method loads the param grid for the sp node selected
        /// in the tree view.
        /// </summary>
        private void LoadData(string spName)
        {
            List<string> paramNames;
            List<string> paramTypes;
            ReadProcMetaData(spName, out paramNames, out paramTypes);

            AddGridRows(ref paramNames, ref paramTypes);
        }

        /// <summary>
        /// Method for adding the parameter detail rows to the grid.
        /// </summary>
        private void AddGridRows(ref List<string> paramNames, ref List<string> paramTypes)
        {
            this.paramGrid.Rows.Clear();
            for (int i = 0; i < paramNames.Count; ++i)
            {
                DataGridViewCell cell1 = new DataGridViewTextBoxCell();
                cell1.Value = paramNames[i];

                DataGridViewCell cell2 = new DataGridViewTextBoxCell();
                cell2.Value = paramTypes[i];

                DataGridViewCell cell3 = new DataGridViewTextBoxCell();

                DataGridViewRow row = new DataGridViewRow();
                row.Cells.Add(cell1);
                row.Cells.Add(cell2);
                row.Cells.Add(cell3);

                cell1.ReadOnly = true;
                cell2.ReadOnly = true;
                this.paramGrid.Rows.Add(row);
            }
        }

        #endregion

        #region Private DB Related Methods

        /// <summary>
        /// This method reads the parameter details for the SP.
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paramNames"></param>
        /// <param name="paramTypes"></param>
        private void ReadProcMetaData(string procName, out List<string> paramNames, out List<string> paramTypes)
        {
            string sqlString = "SELECT SCHEMA_NAME(schema_id) AS schema_name, o.name AS object_name, p.name AS parameter_name, TYPE_NAME(p.user_type_id) AS parameter_type FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id = OBJECT_ID('{0}')";
            
            paramNames = new List<string>();
            paramTypes = new List<string>();

            // Check if the connection object is valid
            if(null == this.DatabaseConnection || 
                this.DatabaseConnection.State != System.Data.ConnectionState.Open)
                throw new ArgumentException("Database connection not valid");

            string query = string.Format(sqlString, procName);
            SqlCommand cmd = new SqlCommand(query, this.DatabaseConnection);
            cmd.CommandType = System.Data.CommandType.Text;

            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                paramNames.Add(reader[2].ToString());
                paramTypes.Add(reader[3].ToString());
            }
            reader.Close();
        }

        /// <summary>
        /// This method gets the name of all stored procedures for the
        /// database and classifies them in two lists for sys and dbo
        /// procedures.
        /// </summary>
        /// <param name="connString"></param>
        /// <param name="dboProcs"></param>
        /// <param name="sysProcs"></param>
        private void ReadSQLMetaData(out List<string> dboProcs, out List<string> sysProcs)
        {
            dboProcs = new List<string>();
            sysProcs = new List<string>();

            // Check if the connection object is valid
            if (null == this.DatabaseConnection ||
                this.DatabaseConnection.State != System.Data.ConnectionState.Open)
                throw new ArgumentException("Database connection not valid");            
            
            SqlCommand cmd = new SqlCommand("sp_stored_procedures", this.DatabaseConnection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (reader[1].ToString() == "sys")
                    sysProcs.Add(reader[2].ToString());
                else
                    dboProcs.Add(reader[2].ToString());
            }
            reader.Close();
            cmd.Dispose();
        }

        #endregion
    }
}
